SSIS - move only the files names returned in SQL command
I have a SQL statement that returns 600 PDF filenames that I need to use to move a copy of the file out of folder into a new folder (The filenames in the SQL are the exact name of the PDF files in the folder).
I have a execute SQL statement set up that passes the PDF name result set to a variable.
How can I use this variable to pass to file system task to only copy out the PDF filenames returned in SQL statement and passed to variable?
Thanks,
Also I am getting an eror
ssis
asked Nov 15 '18 at 21:23
user3772443user3772443
64118
add a comment |
I have a SQL statement that returns 600 PDF filenames that I need to use to move a copy of the file out of folder into a new folder (The filenames in the SQL are the exact name of the PDF files in the folder).
I have a execute SQL statement set up that passes the PDF name result set to a variable.
How can I use this variable to pass to file system task to only copy out the PDF filenames returned in SQL statement and passed to variable?
Thanks,
Also I am getting an eror
ssis
asked Nov 15 '18 at 21:23
user3772443user3772443
64118
If you're getting an error, you should tell us what it is. What's the error code and description text?
– digital.aaron
Nov 15 '18 at 23:45
add a comment |
I have a SQL statement that returns 600 PDF filenames that I need to use to move a copy of the file out of folder into a new folder (The filenames in the SQL are the exact name of the PDF files in the folder).
I have a execute SQL statement set up that passes the PDF name result set to a variable.
How can I use this variable to pass to file system task to only copy out the PDF filenames returned in SQL statement and passed to variable?
Thanks,
Also I am getting an eror
ssis
asked Nov 15 '18 at 21:23
user3772443user3772443
64118
I have a SQL statement that returns 600 PDF filenames that I need to use to move a copy of the file out of folder into a new folder (The filenames in the SQL are the exact name of the PDF files in the folder).
I have a execute SQL statement set up that passes the PDF name result set to a variable.
How can I use this variable to pass to file system task to only copy out the PDF filenames returned in SQL statement and passed to variable?
Thanks,
Also I am getting an eror
ssis
ssis
asked Nov 15 '18 at 21:23
user3772443user3772443
64118
asked Nov 15 '18 at 21:23
user3772443user3772443
64118
asked Nov 15 '18 at 21:23
user3772443user3772443
64118
asked Nov 15 '18 at 21:23
user3772443user3772443
64118
asked Nov 15 '18 at 21:23
user3772443user3772443
64118
64118
If you're getting an error, you should tell us what it is. What's the error code and description text?
– digital.aaron
Nov 15 '18 at 23:45
add a comment |
If you're getting an error, you should tell us what it is. What's the error code and description text?
– digital.aaron
Nov 15 '18 at 23:45
If you're getting an error, you should tell us what it is. What's the error code and description text?
– digital.aaron
Nov 15 '18 at 23:45
If you're getting an error, you should tell us what it is. What's the error code and description text?
– digital.aaron
Nov 15 '18 at 23:45
add a comment |
1 Answer
1
active
oldest
votes
This is a pretty simple design pattern in SSIS. Generally speaking, you'll get your list of file names from an Execute SQL Task, set up a Foreach Loop Container to process each file, and a File System Task inside the loop.
To start, you'll want to make sure you have the following variables:
I'm using E:Import as my source folder. That's where I expect all my files to be. You can change this to be the folder where your files are. I want to move files into a subfolder, so I've set the DestinationFolder to E:ImportInternal. Again, change this to suit your needs.
Additionally, you can see we have a Filenames variable of type System.Object. This is the ADO object that will hold the results of our SQL query. The Filename string variable will be used to store each file name as we go through the loop.
SourcePath and DestinationPath we're going to configure to be populated by an expression. We're going to concatenate the folder names with our file names. To do that, open the variables window, and click on SourcePath. Then edit the Properties and set EvaluateAsExpression to True, and then set the expression to be @[User::SourceFolder] + @[User::Filename]. Do the same for DestinationPath, using the @[User::DestinationFolder] variable in the expression. You should end up with your two *Path variables looking something like this:
Now we can configure the Execute SQL Task. Make sure you set the ResultSet value to Full result set and map the results with Result Name equal to 0 to your ADO object variable Filenames.
Next, create a Foreach Loop Container and configure it to use the Foreach ADO Enumerator type and point it to your Filenames variable.
Go to Variable Mappings and make sure to map User::Filename to Index 0.
Finally, create a File System Task and put it inside the loop container. Then configure it to use your *Path variables with the appropriate action.
When you're all done, you should having a package that looks like this:
answered Nov 16 '18 at 0:21
digital.aarondigital.aaron
3,30711332
Thank you!. it worked great. only thing missing was the +"\"+ between the Folder paths and file names
– user3772443
Nov 16 '18 at 19:26
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53328093%2fssis-move-only-the-files-names-returned-in-sql-command%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This is a pretty simple design pattern in SSIS. Generally speaking, you'll get your list of file names from an Execute SQL Task, set up a Foreach Loop Container to process each file, and a File System Task inside the loop.
To start, you'll want to make sure you have the following variables:
I'm using E:Import as my source folder. That's where I expect all my files to be. You can change this to be the folder where your files are. I want to move files into a subfolder, so I've set the DestinationFolder to E:ImportInternal. Again, change this to suit your needs.
Additionally, you can see we have a Filenames variable of type System.Object. This is the ADO object that will hold the results of our SQL query. The Filename string variable will be used to store each file name as we go through the loop.
SourcePath and DestinationPath we're going to configure to be populated by an expression. We're going to concatenate the folder names with our file names. To do that, open the variables window, and click on SourcePath. Then edit the Properties and set EvaluateAsExpression to True, and then set the expression to be @[User::SourceFolder] + @[User::Filename]. Do the same for DestinationPath, using the @[User::DestinationFolder] variable in the expression. You should end up with your two *Path variables looking something like this:
Now we can configure the Execute SQL Task. Make sure you set the ResultSet value to Full result set and map the results with Result Name equal to 0 to your ADO object variable Filenames.
Next, create a Foreach Loop Container and configure it to use the Foreach ADO Enumerator type and point it to your Filenames variable.
Go to Variable Mappings and make sure to map User::Filename to Index 0.
Finally, create a File System Task and put it inside the loop container. Then configure it to use your *Path variables with the appropriate action.
When you're all done, you should having a package that looks like this:
answered Nov 16 '18 at 0:21
digital.aarondigital.aaron
3,30711332
Thank you!. it worked great. only thing missing was the +"\"+ between the Folder paths and file names
– user3772443
Nov 16 '18 at 19:26
add a comment |
This is a pretty simple design pattern in SSIS. Generally speaking, you'll get your list of file names from an Execute SQL Task, set up a Foreach Loop Container to process each file, and a File System Task inside the loop.
To start, you'll want to make sure you have the following variables:
I'm using E:Import as my source folder. That's where I expect all my files to be. You can change this to be the folder where your files are. I want to move files into a subfolder, so I've set the DestinationFolder to E:ImportInternal. Again, change this to suit your needs.
Additionally, you can see we have a Filenames variable of type System.Object. This is the ADO object that will hold the results of our SQL query. The Filename string variable will be used to store each file name as we go through the loop.
SourcePath and DestinationPath we're going to configure to be populated by an expression. We're going to concatenate the folder names with our file names. To do that, open the variables window, and click on SourcePath. Then edit the Properties and set EvaluateAsExpression to True, and then set the expression to be @[User::SourceFolder] + @[User::Filename]. Do the same for DestinationPath, using the @[User::DestinationFolder] variable in the expression. You should end up with your two *Path variables looking something like this:
Now we can configure the Execute SQL Task. Make sure you set the ResultSet value to Full result set and map the results with Result Name equal to 0 to your ADO object variable Filenames.
Next, create a Foreach Loop Container and configure it to use the Foreach ADO Enumerator type and point it to your Filenames variable.
Go to Variable Mappings and make sure to map User::Filename to Index 0.
Finally, create a File System Task and put it inside the loop container. Then configure it to use your *Path variables with the appropriate action.
When you're all done, you should having a package that looks like this:
answered Nov 16 '18 at 0:21
digital.aarondigital.aaron
3,30711332
Thank you!. it worked great. only thing missing was the +"\"+ between the Folder paths and file names
– user3772443
Nov 16 '18 at 19:26
add a comment |
This is a pretty simple design pattern in SSIS. Generally speaking, you'll get your list of file names from an Execute SQL Task, set up a Foreach Loop Container to process each file, and a File System Task inside the loop.
To start, you'll want to make sure you have the following variables:
I'm using E:Import as my source folder. That's where I expect all my files to be. You can change this to be the folder where your files are. I want to move files into a subfolder, so I've set the DestinationFolder to E:ImportInternal. Again, change this to suit your needs.
Additionally, you can see we have a Filenames variable of type System.Object. This is the ADO object that will hold the results of our SQL query. The Filename string variable will be used to store each file name as we go through the loop.
SourcePath and DestinationPath we're going to configure to be populated by an expression. We're going to concatenate the folder names with our file names. To do that, open the variables window, and click on SourcePath. Then edit the Properties and set EvaluateAsExpression to True, and then set the expression to be @[User::SourceFolder] + @[User::Filename]. Do the same for DestinationPath, using the @[User::DestinationFolder] variable in the expression. You should end up with your two *Path variables looking something like this:
Now we can configure the Execute SQL Task. Make sure you set the ResultSet value to Full result set and map the results with Result Name equal to 0 to your ADO object variable Filenames.
Next, create a Foreach Loop Container and configure it to use the Foreach ADO Enumerator type and point it to your Filenames variable.
Go to Variable Mappings and make sure to map User::Filename to Index 0.
Finally, create a File System Task and put it inside the loop container. Then configure it to use your *Path variables with the appropriate action.
When you're all done, you should having a package that looks like this:
answered Nov 16 '18 at 0:21
digital.aarondigital.aaron
3,30711332
This is a pretty simple design pattern in SSIS. Generally speaking, you'll get your list of file names from an Execute SQL Task, set up a Foreach Loop Container to process each file, and a File System Task inside the loop.
To start, you'll want to make sure you have the following variables:
I'm using E:Import as my source folder. That's where I expect all my files to be. You can change this to be the folder where your files are. I want to move files into a subfolder, so I've set the DestinationFolder to E:ImportInternal. Again, change this to suit your needs.
Additionally, you can see we have a Filenames variable of type System.Object. This is the ADO object that will hold the results of our SQL query. The Filename string variable will be used to store each file name as we go through the loop.
SourcePath and DestinationPath we're going to configure to be populated by an expression. We're going to concatenate the folder names with our file names. To do that, open the variables window, and click on SourcePath. Then edit the Properties and set EvaluateAsExpression to True, and then set the expression to be @[User::SourceFolder] + @[User::Filename]. Do the same for DestinationPath, using the @[User::DestinationFolder] variable in the expression. You should end up with your two *Path variables looking something like this:
Now we can configure the Execute SQL Task. Make sure you set the ResultSet value to Full result set and map the results with Result Name equal to 0 to your ADO object variable Filenames.
Next, create a Foreach Loop Container and configure it to use the Foreach ADO Enumerator type and point it to your Filenames variable.
Go to Variable Mappings and make sure to map User::Filename to Index 0.
Finally, create a File System Task and put it inside the loop container. Then configure it to use your *Path variables with the appropriate action.
When you're all done, you should having a package that looks like this:
answered Nov 16 '18 at 0:21
digital.aarondigital.aaron
3,30711332
answered Nov 16 '18 at 0:21
digital.aarondigital.aaron
3,30711332
answered Nov 16 '18 at 0:21
digital.aarondigital.aaron
3,30711332
answered Nov 16 '18 at 0:21
digital.aarondigital.aaron
3,30711332
3,30711332
Thank you!. it worked great. only thing missing was the +"\"+ between the Folder paths and file names
– user3772443
Nov 16 '18 at 19:26
add a comment |
Thank you!. it worked great. only thing missing was the +"\"+ between the Folder paths and file names
– user3772443
Nov 16 '18 at 19:26
Thank you!. it worked great. only thing missing was the +"\"+ between the Folder paths and file names
– user3772443
Nov 16 '18 at 19:26
Thank you!. it worked great. only thing missing was the +"\"+ between the Folder paths and file names
– user3772443
Nov 16 '18 at 19:26
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53328093%2fssis-move-only-the-files-names-returned-in-sql-command%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
If you're getting an error, you should tell us what it is. What's the error code and description text?
– digital.aaron
Nov 15 '18 at 23:45