satya - 12/22/2019, 3:07:27 PM
During install pick this to use SSIS later: Data storage and processing
SSIS: Don't ask me why!
satya - 12/22/2019, 1:24:24 PM
what I installed?
SQL Server 2019 (1.5G of install)
Visual Studio 2019 (9G of install)
Install it on wifi! :)
satya - 12/22/2019, 1:52:00 PM
Strange things Visual Studio 2019 and SSIS
1. Documentation seem to indicate SSIS is installed along with visual studio 2019
2. However a "new project" doesn't show SSIS as a project option
3. It appears one has to use "extension manager" in vs to download again!!! wtf?? another 500MB of download on verizon bandwidth!!
4. So I did it anyway. it kicks me to browser to download it leaving aside the vs (wow! who designed this? Certainly a comity)
5. Then it seem to complain some strange processes are running for installation to proceed.
6. I closed vs. still issues. then i have to close "ie" the very browser that started this
7. It says it completed the setup!!!
9. wow!! or wtf!!
satya - 12/22/2019, 1:55:42 PM
Here are some of these vows explained
satya - 12/22/2019, 2:38:40 PM
This is also discussed at length here on VS docs on Github
satya - 12/22/2019, 2:45:33 PM
SSIS Tech community page from MS
SSIS Tech community page from MS
You will see short and long postings on many aspects of SSIS
satya - 12/22/2019, 3:07:27 PM
During install pick this to use SSIS later: Data storage and processing
satya - 12/22/2019, 3:12:26 PM
SSIS is primarily documented here: tutorials, lesson plans etc from MS
SSIS is primarily documented here: tutorials, lesson plans etc from MS
satya - 12/22/2019, 3:13:31 PM
SSIS VS 2019 Marketplace extension home page is here
satya - 12/22/2019, 3:16:06 PM
In that tutorial creating a new ssis project is here
satya - 12/22/2019, 3:43:31 PM
If you installed VS 2019, then the marketplace will show this
However I got this picture after "downloading" the extension. So you may see "download" against it as opposed to a green check box. It is not clear why it says download, even though the vs 2019 installed it using the data storage work loads
satya - 12/22/2019, 3:44:47 PM
Once this is installed as above, now a new project will show the right project type
satya - 12/22/2019, 4:23:27 PM
Directory structure of an SSIS project
satya - 12/22/2019, 4:23:56 PM
it looks like this
satya - 1/12/2020, 11:34:13 AM
Creating first project
1. For a project template, search for "integration" as the key word. Don't use "ssis" for that is too narrow
2. There is an SSIS version that is for azure, so make sure you pick the non azure one if your goal is a plain ssis project template
3. vs will create a sub directory with the project name, so locate the parent directory or repo where you want to put this stuff.
4. You can see the details of the created artifacts on the right side in the solution explorer, which you can see above as well.
5. You can rename the created "package.dtsx" to a relevant name as you can add more packages to this project
satya - 1/12/2020, 12:14:30 PM
SSIS convert datetime from ISO format
satya - 1/12/2020, 12:14:40 PM
SSIS convert datetime from ISO format
SSIS convert datetime from ISO format
satya - 1/12/2020, 1:23:15 PM
Here is how you add an OLEDB connection manager on localhost to SSIS
Here is how you add an OLEDB connection manager on localhost to SSIS
satya - 1/12/2020, 2:10:50 PM
How can I add additional columns to an ssis input source?
How can I add additional columns to an ssis input source?
Search for: How can I add additional columns to an ssis input source?
satya - 1/13/2020, 11:42:34 AM
How can I map the same input field to multiple output fields in SSIS
How can I map the same input field to multiple output fields in SSIS
Search for: How can I map the same input field to multiple output fields in SSIS
satya - 1/13/2020, 12:06:23 PM
SSIS mapping expression language examples
SSIS mapping expression language examples
satya - 1/13/2020, 12:06:43 PM
How can I create a derived column from 2 columns in SSIS
How can I create a derived column from 2 columns in SSIS
Search for: How can I create a derived column from 2 columns in SSIS
satya - 1/13/2020, 12:12:33 PM
Script component is documented here
satya - 1/13/2020, 12:12:59 PM
Derived column transformation is here
satya - 1/13/2020, 12:22:06 PM
warning ssis: One or more columns do not have an alias assigned
warning ssis: One or more columns do not have an alias assigned
Search for: warning ssis: One or more columns do not have an alias assigned
satya - 1/13/2020, 12:24:42 PM
what are typecast functions in SSIS?
what are typecast functions in SSIS?
satya - 1/13/2020, 12:38:47 PM
Data Conversion conversation on SOF
satya - 1/13/2020, 1:29:15 PM
Essentials so far
1. From a csv file insert into an equivalent table that has an identity column
2. Flat file connection manager. Defines columns in the file and their data types. ISO date time stamp points to DT_Date type
3. OLE DB connection manager. for the database.
4. Flat file source reading from the file.
5. Columns are not sufficient to populate the table as the table has additional columns
6. Need a derived column transformation. You cannot map one column to multiple columns. So you have to have each column listed here, even though they are duplicate data but with different column names. ex: last_updated_by, created_by where both point to the logged in user.
7. system variables like the logged in user @[System::CreatorName] can be used as values to the derived columns
8. Finally the OLEDB destination with a table or view. Here the mappings have to be one to one. (see above). There is a strange behavior with keep identity. when you want the identity to be automatically updated, turn off this flag and use "ignore" as the input to this identity column.
satya - 1/14/2020, 11:44:22 AM
Execute SQL Task ssis
Execute SQL Task ssis
satya - 1/14/2020, 11:46:57 AM
Script Task in SSIS
Script Task in SSIS
satya - 1/14/2020, 11:49:51 AM
Script Component in SSIS
Script Component in SSIS
satya - 1/14/2020, 11:50:01 AM
Script Component is documented here
satya - 1/14/2020, 11:50:19 AM
Using a stored procedure to ingest data into SQL Server using SSIS
Using a stored procedure to ingest data into SQL Server using SSIS
Search for: Using a stored procedure to ingest data into SQL Server using SSIS
satya - 1/14/2020, 11:53:09 AM
Using a stored procedure to ingest data into SQL Server using SSIS
1. Folks may use OLE DB Command to execute a stored proc for each row
2. This will be expensive as that many stored procs will be called
3. Suggested solutions include using a staging table and run the procs in side the database for each row
4. An alternative to 3 is to use a similar approach using c# script code and Data Table object of .net to call the stored proc with the table as an input
satya - 1/14/2020, 11:53:27 AM
is there a bulk stored proc call in SSIS?
is there a bulk stored proc call in SSIS?
satya - 1/14/2020, 12:00:02 PM
Here are some notes on bulk inserts
satya - 1/14/2020, 1:40:45 PM
How to store objects with rules in a relational database: an ETL question
How to store objects with rules in a relational database: an ETL question
Search for: How to store objects with rules in a relational database: an ETL question
satya - 1/14/2020, 1:45:04 PM
How to store objects with rules in a relational database: an ETL question
1. Entities in a relational database are actually collection of objects that are related
2. when they are individually constructed by transactional systems stored procedures can construct one at a time with all the rules applied and foreign keys, parent keys, and child keys satisfied.
3. what do you do when you try to load a million of them?? Calling stored procs one at a takes too long
4. How do you flatten the objects? where do you apply the business rules? In the database or outside the database?
5. i wonder, if the solution is to apply them in a layer prior to the target database where 1) first the rules are applied on an object by object basis in parallel 2) then splice the objects into multiple streams where each stream is fully constructed accurate table. 3) Then load them using bulk. Just wonder!!!
satya - 1/14/2020, 1:46:19 PM
what is the difference between command and a task in ssis
what is the difference between command and a task in ssis
Search for: what is the difference between command and a task in ssis
satya - 1/29/2020, 5:56:36 PM
How do I pass variables between ssis tasks?
How do I pass variables between ssis tasks?
satya - 1/29/2020, 5:56:59 PM
Perhaps there is something in this article
satya - 1/29/2020, 6:05:21 PM
Few notes
1. variables have scope
2. at the package level or task level
3. right click on task to set a break point
4. tasks can define variables which can be set against parameters
satya - 1/29/2020, 6:06:33 PM
user scope in ssis variables
user scope in ssis variables
satya - 1/29/2020, 6:09:07 PM
what is user:: in ssis variables
what is user:: in ssis variables
satya - 1/29/2020, 6:16:23 PM
Notes
1. variables can be system defined or user defined
2. users can define variables at a package, task, container etc.
satya - 1/29/2020, 6:17:44 PM
On a variable
1. name
2. namespace(This is where I have doubts on)
3. whether it can raise on event on value change
4. can be an expression
satya - 1/29/2020, 6:18:46 PM
And there it is, the namespace discussion
Integration Services provides two namespaces, User and System. By default, custom variables are in the User namespace, and system variables are in the System namespace. You can create additional namespaces for user-defined variables and change the name of the User namespace, but you cannot change the name of the System namespace, add variables to the System namespace, or assign system variables to a different namespace.
satya - 1/29/2020, 6:19:03 PM
Only 2 namespaces: user and system
Only 2 namespaces: user and system
satya - 1/29/2020, 6:20:19 PM
Oh, sorry, looks like you can define additional namespaces if you like
Oh, sorry, looks like you can define additional namespaces if you like
satya - 1/29/2020, 6:41:40 PM
How do I list the files that an ftp task just received in ssis?
How do I list the files that an ftp task just received in ssis?
Search for: How do I list the files that an ftp task just received in ssis?